<?php

namespace AdminBundle\Controller;

use Symfony\Component\HttpFoundation\Request;

class ArticleController extends AdminController
{
    /*
     * 首页
     */
    public function indexAction()
    {

        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }


        $conn = $this->get("database_connection");
        $admin_role_id = $this->get("session")->get("admin_role_id");

        $this_admin_id = $this->get("session")->get("admin_id");
        $this_district_id = $this->get("session")->get("district_id");

        // 获取企业数量
        if($admin_role_id == 4){

            $sql = "SELECT * FROM company WHERE admin_id = '". $this_admin_id ."' ";
        } else {
            $sql = "SELECT * FROM company WHERE district_id = '". $this_district_id ."' ";
        }

        $data['company_count'] = count($conn->fetchAll($sql));

        // 获取企业联络人数量
        $company_sql = "SELECT u.*, c.name as company_name FROM user u LEFT JOIN company c ON u.company_id = c.id WHERE 1 ";

        if($admin_role_id == 4){
            $company_sql .= "AND u.company_id IN (SELECT id FROM company WHERE admin_id = '". $this_admin_id ."')";
        } else {
            $company_sql .= "AND u.district_id = '". $this_district_id ."'";
        }

        $data['company_member_count'] = count($conn->fetchAll($company_sql));


        // 科技联络人
        $teca_sql = $company_sql . " AND u.technology_liaisons_status = 1 ";
        $data['teac_member_count'] = count($conn->fetchAll($teca_sql));

        // 申报项目数量
        if($admin_role_id == 4){
            $project_sql = "SELECT * FROM project WHERE admin_id = '". $this_admin_id ."'";

        } else {
            $project_sql = "SELECT * FROM project WHERE district_id = '". $this_district_id ."'";
        }

        $data['project_count'] = count($conn->fetchAll($project_sql));

        // 最新消息  需要排序
        $article_sql = "SELECT title, DATE_FORMAT(created_at,'%Y.%m.%d') as created_time, id  FROM article WHERE";
        if($admin_role_id != 4){
            $article_sql .= " district_id = '". $this_district_id ."' ";

        } else {
            $article_sql .= " admin_id = '". $this_admin_id ."' ";
        }
        $article_sql .= " ORDER BY created_at DESC LIMIT 10 ";
        $data['article'] = $conn->fetchAll($article_sql);


        // 最新文件 需要排序
        $file_sql = "SELECT *, DATE_FORMAT(created_at,'%Y.%m.%d') as created_time FROM file WHERE file_category_id IN (SELECT id FROM file_category WHERE";
        if($admin_role_id == 4){

            $file_sql .= " admin_id = '". $this_admin_id ."' )";
        } else {

            $file_sql .= " district_id = '". $this_district_id ."') ";
        }

        $file_sql .= " ORDER BY created_at DESC LIMIT 10 ";

        $data['file'] = $conn->fetchAll($file_sql);

        // 最新企业 需要排序
        $new_company_sql = "SELECT *, DATE_FORMAT(created_at,'%Y.%m.%d') as created_time FROM company WHERE 1";
        if($admin_role_id == 4){

            $new_company_sql .= " AND admin_id = '". $this_admin_id ."'";
        } else {
            $new_company_sql .= " AND district_id = '". $this_district_id ."'";
        }
        $new_company_sql .= " ORDER BY created_at DESC LIMIT 10 ";

        $data['new_company'] = $conn->fetchAll($new_company_sql);

        
        return $this->render('AdminBundle::homepage.html.twig',array("data"=>$data));
    }


    /*
     * 公告消息列表
     */
    public function noticeListAction()
    {
//        $admin = $this->getApiData("ApiBundle:Admin/Auth:selfInformation");

        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }

        $this_admin_id = $this->get("session")->get("admin_id");
        $this_district_id = $this->get("session")->get("district_id");
        $admin_role_id = $this->get("session")->get("admin_role_id");

        // 消息总数
        $conn = $this->get("database_connection");

        // 本年培训人数
        $year_sql = "SELECT count(*) FROM apply WHERE user_id IN (SELECT id FROM user WHERE";

        //本年培训企业数
        $company_sql = "SELECT count(*) FROM apply WHERE company_name IN (SELECT name FROM company WHERE";


        if($admin_role_id != 4){
            $sql = "SELECT count(*) FROM article WHERE district_id = '". $this_district_id ."'  ";

            $year_sql .= " district_id = '". $this_district_id ."')";

            $company_sql .= " district_id = '". $this_district_id ."') ";

        } else {
            $sql = "SELECT count(*) FROM article WHERE admin_id = '". $this_admin_id ."' ";

            $year_sql .= " company_id IN (SELECT id FROM company WHERE admin_id = '". $this_admin_id ."') )";
            $company_sql .= " admin_id = '". $this_admin_id ."') ";
        }
        $all_count = $conn->fetchColumn($sql);

        $curr_month = date("Y-m");
        $month_sql = $sql . " AND DATE_FORMAT(created_at,'%Y-%m') = '". $curr_month ."'";

        $month_count = $conn->fetchColumn($month_sql);

        //本年培训人数
        $now_year = date("Y");
        $year_sql .= " AND DATE_FORMAT(created_at,'%Y') = '". $now_year ."' ";
        $year_count = $conn->fetchColumn($year_sql);

        // 本年培训企业数
        $company_sql .= " AND DATE_FORMAT(created_at,'%Y') = '". $now_year ."' ";
        $company_sql .= "GROUP BY company_name";
        $year_company_count = $conn->fetchColumn($company_sql);

        return $this->render('AdminBundle:Article:noticeList.html.twig',array('all_count'=>$all_count,'month_count'=>$month_count,"year_count"=>$year_count,"year_company_count"=>$year_company_count));
    }


    /*
     * 公告消息详情
     */
    public function noticeDetailAction(Request $request,$id)
    {
        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }
        $data = $this->getApiData("ApiBundle:Admin/Article:articleDetail",array("id" => $id));
        return $this->render("AdminBundle:Article:noticeDetail.html.twig",array("data" => $data));
    }

    /*
     * 发布消息 1.公告 2.培训
     */
    public function noticeReleaseAction($type)
    {
        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }

        $conn = $this->get("database_connection");
        $data["category"] = $conn->fetchAll("SELECT * FROM company_category WHERE 1 ");
        $data["attribute"] = $conn->fetchAll("SELECT * FROM company_attribute WHERE 1 ");
        $data['teac_admin'] = $conn->fetchAll("SELECT id,nickname FROM admin WHERE admin_role_id = 4 AND district_id = '". $this->get("session")->get("district_id") ."'");


        return $this->render("AdminBundle:Article:noticeRelease.html.twig",array("data"=>$data,"type"=>$type,"curr_date"=>date('Y-m-d')));
    }


    /*
     * 培训消息列表
     */
    public function trainingListAction()
    {
        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }

        $this_admin_id = $this->get("session")->get("admin_id");
        $this_district_id = $this->get("session")->get("district_id");
        $admin_role_id = $this->get("session")->get("admin_role_id");

        // 消息总数
        $conn = $this->get("database_connection");

        // 本年培训人数
        $year_sql = "SELECT count(*) FROM apply WHERE user_id IN (SELECT id FROM user WHERE";

        //本年培训企业数
        $company_sql = "SELECT count(*) FROM apply WHERE company_name IN (SELECT name FROM company WHERE";

        if($admin_role_id != 4){
            $sql = "SELECT count(*) FROM article WHERE training_id IS NOT NULL AND district_id = '". $this_district_id ."'  ";
            $year_sql .= " district_id = '". $this_district_id ."')";

            $company_sql .= " district_id = '". $this_district_id ."') ";
        } else {
            $sql = "SELECT count(*) FROM article WHERE training_id IS NOT NULL AND admin_id = '". $this_admin_id ."' ";

            $year_sql .= " company_id IN (SELECT id FROM company WHERE admin_id = '". $this_admin_id ."') )";
            $company_sql .= " admin_id = '". $this_admin_id ."') ";
        }
        $all_count = $conn->fetchColumn($sql);

        $curr_month = date("Y-m");
        $month_sql = $sql . " AND DATE_FORMAT(created_at,'%Y-%m') = '". $curr_month ."'";
        $month_count = $conn->fetchColumn($month_sql);

        //本年培训人数
        $now_year = date("Y");
        $year_sql .= " AND DATE_FORMAT(created_at,'%Y') = '". $now_year ."' ";
        $year_count = $conn->fetchColumn($year_sql);

        // 本年培训企业数
        $company_sql .= " AND DATE_FORMAT(created_at,'%Y') = '". $now_year ."' ";
        $company_sql .= "GROUP BY company_name";
        $year_company_count = $conn->fetchColumn($company_sql);

        return $this->render('AdminBundle:Article:trainingList.html.twig',array('all_count'=>$all_count,'month_count'=>$month_count,"year_count"=>$year_count,"year_company_count"=>$year_company_count));
    }


    /*
     * 培训消息详情
     */
    public function trainingDetailAction($id)
    {
        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }
        $data = $this->getApiData("ApiBundle:Admin/Article:articleDetail",array("id"=>$id));
        return $this->render("AdminBundle:Article:trainingDetail.html.twig",array("data"=>$data));
    }


    /*
     * 消息人员列表- type 1 公告未读人员 2 公告已读人员  3 培训未读人员   4 培训已读人员 5 培训未报名人员 6 培训已报名人员
     */
    public function trainingReadListAction($id,$type,$page)
    {
        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }

        if($type != 5 && $type != 6){

            if($type == 1 || $type == 3){
                $is_read = 0;
            } else {
                $is_read = 1;
            }

            $data = $this->getApiData("ApiBundle:Admin/Article:getReadList",array("id"=>$id,"is_read"=>$is_read,"page"=>$page));
        } else {

            if($type == 5){

                $is_apply = 0;
            } else {
                $is_apply = 1;
            }

            $data = $this->getApiData("ApiBundle:Admin/Article:getApllyList",array("id"=>$id,"is_apply"=>$is_apply));
        }


        return $this->render("AdminBundle:Article:trainingReadList.html.twig",array("type"=>$type,"data"=>$data));
    }
}
